#!/usr/bin/env python
# -*- coding: utf-8 -*-

from flask import Blueprint, request, jsonify, send_file
from datetime import datetime, timedelta
from app.models import  User
from app.core.extensions import db
from sqlalchemy import func, desc, case
import xlsxwriter
from io import BytesIO

from app.models.agents import ActivationCode, Agent

# 创建蓝图
sales_bp = Blueprint('admin_sales', __name__)


@sales_bp.route('/stats', methods=['GET'])
def get_sales_stats():
    """
    获取销售统计数据
    ---
    参数:
      - agent_id: 代理ID
      - period/dimension: 统计周期(day/week/month/year)，默认month
      - range: 时间范围(week/month/quarter/year)，默认month
      - type: 数据类型(overview/trend/ranking)
      - limit: 排名限制数量（用于ranking类型）
      - startDate: 开始日期
      - endDate: 结束日期
    返回:
      - code: 0表示成功
      - data: 根据请求参数返回不同数据
    """
    # 从请求参数获取
    agent_id = request.args.get('agent_id')
    # 兼容dimension和period参数
    period = request.args.get('period') or request.args.get('dimension', 'month')
    data_type = request.args.get('type', 'trend')
    limit = int(request.args.get('limit', 10))

    # 处理range参数设定时间范围
    time_range = request.args.get('range', 'month')
    start_date = request.args.get('startDate')
    end_date = request.args.get('endDate')

    try:
        # 获取当前日期
        now = datetime.now()

        # 如果提供了明确的起止日期，则使用它们
        if start_date:
            start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        else:
            # 根据range参数设置起始日期
            if time_range == 'week':
                start_datetime = now - timedelta(days=7)
            elif time_range == 'month':
                start_datetime = now - timedelta(days=30)
            elif time_range == 'quarter':
                start_datetime = now - timedelta(days=90)
            elif time_range == 'year':
                start_datetime = now - timedelta(days=365)
            else:
                # 默认为一个月
                start_datetime = now - timedelta(days=30)

        if end_date:
            end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        else:
            end_datetime = now + timedelta(days=1)

        # 检查是否请求了新的响应格式（带有dimension和range参数的请求）
        if 'dimension' in request.args:
            # 使用新格式返回数据
            return get_new_format_data(agent_id, period, time_range, start_datetime, end_datetime)

        # 按照不同数据类型处理（原有逻辑）
        if data_type == 'overview':
            # 返回概览数据
            return get_overview_data(agent_id, start_datetime, end_datetime)
        elif data_type == 'trend':
            # 返回趋势数据
            return get_trend_data(agent_id, period, start_datetime, end_datetime)
        elif data_type == 'ranking':
            # 返回排名数据
            return get_ranking_data(agent_id, start_datetime, end_datetime, limit)
        else:
            return jsonify({"code": 1, "message": "不支持的数据类型"})

    except Exception as e:
        print(f"获取销售统计数据失败: {str(e)}")
        return jsonify({"code": 500, "message": "获取销售统计数据失败"}), 500


def get_overview_data(agent_id, start_datetime, end_datetime):
    """获取销售概览数据"""
    try:
        # 计算比较周期（上一个周期）
        period_days = (end_datetime - start_datetime).days
        prev_start = start_datetime - timedelta(days=period_days)
        prev_end = start_datetime

        # 查询当前周期数据
        if agent_id:
            # 特定代理的数据 - 已生成的激活码
            curr_codes = db.session.query(func.count(ActivationCode.id)).filter(
                ActivationCode.created_by == agent_id,
                ActivationCode.create_time >= start_datetime,
                ActivationCode.create_time < end_datetime
            ).scalar() or 0

            # 查询当前周期数据 - 已被使用的激活码数量
            curr_sales = db.session.query(func.count(ActivationCode.id)).filter(
                ActivationCode.created_by == agent_id,
                ActivationCode.used_by.isnot(None),
                ActivationCode.create_time >= start_datetime,
                ActivationCode.create_time < end_datetime
            ).scalar() or 0

            # 代理数量
            total_agents = 1

            # 获取访问量（这里简化为激活码数的10倍）
            total_visits = curr_codes * 10 if curr_codes > 0 else 100  # 假设数据

        else:
            # 所有代理的数据 - 已生成的激活码总数
            curr_codes = db.session.query(func.count(ActivationCode.id)).filter(
                ActivationCode.created_by != None,
                ActivationCode.create_time >= start_datetime,
                ActivationCode.create_time < end_datetime
            ).scalar() or 0

            # 查询当前周期数据 - 已被使用的激活码数量
            curr_sales = db.session.query(func.count(ActivationCode.id)).filter(
                ActivationCode.created_by != None,
                ActivationCode.used_by.isnot(None),
                ActivationCode.create_time >= start_datetime,
                ActivationCode.create_time < end_datetime
            ).scalar() or 0

            # 代理数量
            total_agents = db.session.query(func.count(Agent.id)).scalar() or 0

            # 获取访问量（这里简化为激活码数的10倍）
            total_visits = curr_codes * 10 if curr_codes > 0 else 1000  # 假设数据

        # 查询上一周期数据
        if agent_id:
            prev_codes = db.session.query(func.count(ActivationCode.id)).filter(
                ActivationCode.created_by == agent_id,
                ActivationCode.create_time >= prev_start,
                ActivationCode.create_time < prev_end
            ).scalar() or 0

            prev_sales = db.session.query(func.count(ActivationCode.id)).filter(
                ActivationCode.created_by == agent_id,
                ActivationCode.used_by.isnot(None),
                ActivationCode.create_time >= prev_start,
                ActivationCode.create_time < prev_end
            ).scalar() or 0
        else:
            prev_codes = db.session.query(func.count(ActivationCode.id)).filter(
                ActivationCode.created_by != None,
                ActivationCode.create_time >= prev_start,
                ActivationCode.create_time < prev_end
            ).scalar() or 0

            prev_sales = db.session.query(func.count(ActivationCode.id)).filter(
                ActivationCode.created_by != None,
                ActivationCode.used_by.isnot(None),
                ActivationCode.create_time >= prev_start,
                ActivationCode.create_time < prev_end
            ).scalar() or 0

        # 计算同比变化
        sales_trend = calculate_trend(curr_sales, prev_sales)
        codes_trend = calculate_trend(curr_codes, prev_codes)

        # 计算转化率 (已使用激活码 / 总激活码)
        conversion_rate = round((curr_sales / curr_codes) * 100, 2) if curr_codes > 0 else 0
        prev_conversion = round((prev_sales / prev_codes) * 100, 2) if prev_codes > 0 else 0
        conversion_trend = calculate_trend(conversion_rate, prev_conversion)

        # 构建返回数据 - 同时提供新旧字段名以兼容前端
        result = {
            # 新字段名
            "total_activations": float(curr_sales) if curr_sales else 0,
            "total_codes": curr_codes,
            "total_agents": total_agents,
            "activation_rate": conversion_rate,
            "activations_trend": sales_trend,
            "codes_trend": codes_trend,
            "agents_trend": 0,  # 需要查询前一周期代理数进行计算
            "activation_rate_trend": conversion_trend,
            
            # 旧字段名 - 兼容旧版前端
            "totalSales": float(curr_sales) if curr_sales else 0,
            "totalOrders": curr_codes,
            "totalAgents": total_agents,
            "conversionRate": conversion_rate,
            "salesTrend": sales_trend,
            "ordersTrend": codes_trend,
            "agentsTrend": 0,
            "conversionTrend": conversion_trend
        }

        return jsonify({"code": 0, "data": result})

    except Exception as e:
        print(f"获取销售概览数据失败: {str(e)}")
        return jsonify({"code": 500, "message": "获取销售概览数据失败"}), 500


def get_trend_data(agent_id, period, start_datetime, end_datetime):
    """获取销售趋势数据"""
    try:
        labels = []
        sales_data = []
        codes_data = []

        # 根据周期生成时间标签和数据
        if period == 'day':
            # 按天统计
            current_date = start_datetime
            while current_date < end_datetime:
                next_date = current_date + timedelta(days=1)

                # 添加标签
                labels.append(current_date.strftime('%m-%d'))

                # 查询当天的销售额和激活码数
                if agent_id:
                    daily_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_date,
                        ActivationCode.create_time < next_date
                    ).scalar() or 0

                    daily_codes = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.create_time >= current_date,
                        ActivationCode.create_time < next_date
                    ).scalar() or 0
                else:
                    daily_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_date,
                        ActivationCode.create_time < next_date
                    ).scalar() or 0

                    daily_codes = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.create_time >= current_date,
                        ActivationCode.create_time < next_date
                    ).scalar() or 0

                # 添加数据
                sales_data.append(float(daily_sales) if daily_sales else 0)
                codes_data.append(daily_codes)

                # 移到下一天
                current_date = next_date

        elif period == 'week':
            # 按周统计
            # 获取起始日期所在的星期一
            days_to_monday = start_datetime.weekday()
            start_of_week = start_datetime - timedelta(days=days_to_monday)

            current_week = start_of_week
            while current_week < end_datetime:
                end_of_week = current_week + timedelta(days=7)

                # 添加标签 (例如 "05.01-05.07")
                week_label = f"{current_week.strftime('%m.%d')}-{(end_of_week - timedelta(days=1)).strftime('%m.%d')}"
                labels.append(week_label)

                # 查询本周的销售额和激活码数
                if agent_id:
                    weekly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_week,
                        ActivationCode.create_time < end_of_week
                    ).scalar() or 0

                    weekly_codes = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.create_time >= current_week,
                        ActivationCode.create_time < end_of_week
                    ).scalar() or 0
                else:
                    weekly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_week,
                        ActivationCode.create_time < end_of_week
                    ).scalar() or 0

                    weekly_codes = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.create_time >= current_week,
                        ActivationCode.create_time < end_of_week
                    ).scalar() or 0

                # 添加数据
                sales_data.append(float(weekly_sales) if weekly_sales else 0)
                codes_data.append(weekly_codes)

                # 移到下一周
                current_week = end_of_week

        elif period == 'month':
            # 按月统计
            # 获取起始日期所在月份的第一天
            start_of_month = start_datetime.replace(day=1)

            current_month = start_of_month
            while current_month < end_datetime:
                # 计算下个月的第一天
                if current_month.month == 12:
                    next_month = current_month.replace(year=current_month.year + 1, month=1)
                else:
                    next_month = current_month.replace(month=current_month.month + 1)

                # 添加标签
                labels.append(current_month.strftime('%Y-%m'))

                # 查询本月的销售额和激活码数
                if agent_id:
                    monthly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_month,
                        ActivationCode.create_time < next_month
                    ).scalar() or 0

                    monthly_codes = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.create_time >= current_month,
                        ActivationCode.create_time < next_month
                    ).scalar() or 0
                else:
                    monthly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_month,
                        ActivationCode.create_time < next_month
                    ).scalar() or 0

                    monthly_codes = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.create_time >= current_month,
                        ActivationCode.create_time < next_month
                    ).scalar() or 0

                # 添加数据
                sales_data.append(float(monthly_sales) if monthly_sales else 0)
                codes_data.append(monthly_codes)

                # 移到下一月
                current_month = next_month

        elif period == 'year':
            # 按年统计
            # 获取起始日期所在年份的第一天
            start_of_year = start_datetime.replace(month=1, day=1)

            current_year = start_of_year
            while current_year < end_datetime:
                # 计算下一年的第一天
                next_year = current_year.replace(year=current_year.year + 1)

                # 添加标签
                labels.append(str(current_year.year))

                # 查询本年的销售额和激活码数
                if agent_id:
                    yearly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_year,
                        ActivationCode.create_time < next_year
                    ).scalar() or 0

                    yearly_codes = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.create_time >= current_year,
                        ActivationCode.create_time < next_year
                    ).scalar() or 0
                else:
                    yearly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_year,
                        ActivationCode.create_time < next_year
                    ).scalar() or 0

                    yearly_codes = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.create_time >= current_year,
                        ActivationCode.create_time < next_year
                    ).scalar() or 0

                # 添加数据
                sales_data.append(float(yearly_sales) if yearly_sales else 0)
                codes_data.append(yearly_codes)

                # 移到下一年
                current_year = next_year

        # 构建返回数据
        result = {
            "labels": labels,
            "sales": sales_data,
            "orders": codes_data  # 这里的orders实际上是激活码数
        }

        return jsonify({"code": 0, "data": result})

    except Exception as e:
        print(f"获取销售趋势数据失败: {str(e)}")
        return jsonify({"code": 500, "message": "获取销售趋势数据失败"}), 500


def get_ranking_data(agent_id, start_datetime, end_datetime, limit):
    """获取代理销售排名数据"""
    try:
        # 查询时间范围内所有代理的销售额（已被使用的激活码数量）
        agent_sales = db.session.query(
            Agent.id,
            Agent.name,
            func.count(ActivationCode.id).label('total_sales')
        ).join(
            ActivationCode, Agent.id == ActivationCode.created_by
        ).filter(
            ActivationCode.create_time >= start_datetime,
            ActivationCode.create_time < end_datetime,
            ActivationCode.used_by.isnot(None)  # 只计算已激活的
        ).group_by(
            Agent.id
        ).order_by(
            desc('total_sales')
        ).limit(limit).all()

        # 准备返回数据
        agent_names = []
        sales_amounts = []

        for agent in agent_sales:
            agent_names.append(agent.name)
            sales_amounts.append(float(agent.total_sales) if agent.total_sales else 0)

        # 构建返回数据
        result = {
            "agents": agent_names,
            "sales": sales_amounts
        }

        return jsonify({"code": 0, "data": result})

    except Exception as e:
        print(f"获取代理销售排名数据失败: {str(e)}")
        return jsonify({"code": 500, "message": "获取代理销售排名数据失败"}), 500


def calculate_trend(current, previous):
    """计算同比变化百分比"""
    if previous == 0:
        return 100 if current > 0 else 0

    return round(((current - previous) / previous) * 100, 2)


@sales_bp.route('/detail', methods=['GET'])
def get_sales_detail():
    """
    获取销售明细数据
    ---
    参数:
      - agent_id: 代理ID (可选，不提供时获取全部代理的汇总数据)
      - period: 统计周期(day/week/month/year)，默认month
      - start_date: 开始日期(YYYY-MM-DD)
      - end_date: 结束日期(YYYY-MM-DD)
      - status: 状态筛选(all=全部, sold=已售卖, unsold=未售卖)
      - page: 页码，默认1
      - page_size: 每页数量，默认20
    返回:
      - code: 0表示成功
      - data:
        - list: 销售记录列表
          - date: 日期
          - orderCount: 激活码数量
          - salesAmount: 销售额
          - commission: 佣金
          - activationCount: 激活数
          - userCount: 新增用户
          - conversionRate: 转化率
          - averageAmount: 客单价
          - statusData: 状态数据
        - total: 总记录数
    """
    # 从请求参数获取参数
    agent_id = request.args.get('agent_id')
    # 如果agent_id是空字符串，将其设置为None以查询所有代理数据
    if agent_id == '':
        agent_id = None
        
    page = int(request.args.get('page', 1))
    page_size = int(request.args.get('page_size', 20))
    status = request.args.get('status', 'all')
    period = request.args.get('period', 'month')
    
    # 时间范围
    start_date = request.args.get('start_date')
    end_date = request.args.get('end_date')
    
    try:
        # 获取当前日期
        now = datetime.now()
        
        # 如果提供了明确的起止日期，则使用它们
        if start_date:
            start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        else:
            # 默认为过去30天
            if period == 'day':
                start_datetime = now - timedelta(days=1)
            elif period == 'week':
                start_datetime = now - timedelta(days=7)
            elif period == 'month':
                start_datetime = now - timedelta(days=30)
            elif period == 'year':
                start_datetime = now - timedelta(days=365)
            else:
                start_datetime = now - timedelta(days=30)
        
        if end_date:
            end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        else:
            end_datetime = now + timedelta(days=1)
        
        # 如果status不是all，并且提供了agent_id，则直接返回卡密列表
        if status != 'all' and agent_id:
            # 构建查询
            query = ActivationCode.query.filter(
                ActivationCode.create_time >= start_datetime,
                ActivationCode.create_time < end_datetime
            )
            
            # 如果指定了代理ID，则只查询该代理的卡密
            if agent_id:
                query = query.filter(ActivationCode.created_by == agent_id)
            
            # 根据status筛选
            if status == 'sold':
                query = query.filter(ActivationCode.status == 1)  # 已售卖状态
            elif status == 'unsold':
                query = query.filter(ActivationCode.status == 0)  # 未售卖状态
            
            # 计算总记录数
            total = query.count()
            
            # 分页
            codes = query.order_by(ActivationCode.create_time.desc()).offset((page - 1) * page_size).limit(page_size).all()
            
            # 格式化结果
            codes_list = []
            for code in codes:
                codes_list.append({
                    'id': code.id,
                    'code': code.code,
                    'usage_count': code.usage_count, 
                    'status': code.status,
                    'status_text': '已使用' if code.status == 1 else '未使用',
                    'used_by': code.used_by,
                    'user_name': code.user.nick_name if code.user else None,
                    'used_time': code.used_time.strftime('%Y-%m-%d %H:%M:%S') if code.used_time else None,
                    'create_time': code.create_time.strftime('%Y-%m-%d %H:%M:%S'),
                    'expire_time': code.expire_time.strftime('%Y-%m-%d %H:%M:%S') if code.expire_time else None
                })
            
            # 返回卡密列表
            result = {
                "code": 0,
                "data": {
                    "list": codes_list,
                    "total": total
                }
            }
            
            return jsonify(result)
        
        # 以下是按日期统计的逻辑
        # 构建查询
        query = ActivationCode.query.with_entities(
            func.date(ActivationCode.create_time).label('date'),
            func.count(ActivationCode.id).label('code_count'),
            func.sum(case(
                (ActivationCode.status == 1, 1),  # 只计算已使用（已售卖）的卡密
                else_=0
            )).label('sales_amount')  # 已被使用的激活码数量作为销售额
        ).filter(
            ActivationCode.create_time >= start_datetime,
            ActivationCode.create_time < end_datetime
        )
        
        # 如果指定了代理ID，则只查询该代理的数据
        if agent_id:
            query = query.filter(ActivationCode.created_by == agent_id)

        # 按日期分组
        query = query.group_by(func.date(ActivationCode.create_time))

        # 按日期逆序排序
        query = query.order_by(desc('date'))

        # 计算总记录数
        total_query = query.subquery()
        total = ActivationCode.query.with_entities(func.count()).select_from(total_query).scalar() or 0

        # 分页
        paginated_query = query.offset((page - 1) * page_size).limit(page_size)

        # 执行查询
        results = paginated_query.all()

        # 格式化结果
        sales_list = []
        for result in results:
            date_str = result.date.strftime('%Y-%m-%d') if result.date else ''
            sales_amount = float(result.sales_amount) if result.sales_amount else 0
            code_count = result.code_count or 0

            # 计算其他数据
            commission = sales_amount * 0.1  # 假设佣金是销售额的10%
            average_amount = sales_amount / code_count if code_count > 0 else 0

            # 查询该日期已激活的激活码
            activated_filter = [
                func.date(ActivationCode.create_time) == result.date,
                ActivationCode.status == 1  # 已使用状态
            ]
            
            # 如果指定了代理ID，则只查询该代理的数据
            if agent_id:
                activated_filter.append(ActivationCode.created_by == agent_id)
                
            activated_count = ActivationCode.query.with_entities(func.count(ActivationCode.id)).filter(
                *activated_filter
            ).scalar() or 0

            # 查询该日期激活码的各种状态统计
            status_filter = [func.date(ActivationCode.create_time) == result.date]
            
            # 如果指定了代理ID，则只查询该代理的数据
            if agent_id:
                status_filter.append(ActivationCode.created_by == agent_id)
                
            status_counts = ActivationCode.query.with_entities(
                ActivationCode.status,
                func.count(ActivationCode.id).label('count')
            ).filter(
                *status_filter
            ).group_by(ActivationCode.status).all()
            
            # 构建状态统计
            status_data = {}
            for status_item in status_counts:
                status_text = "已售卖" if status_item.status == 1 else "未售卖"
                status_data[status_text] = status_item.count
            
            # 构建销售记录
            sales_record = {
                'date': date_str,
                'orderCount': code_count,  # 生成的激活码总数
                'salesAmount': sales_amount,  # 已售卖的激活码数量
                'commission': commission,
                'activationCount': activated_count,  # 已被使用的激活码数量
                'userCount': activated_count,  # 使用激活码的用户数
                'conversionRate': round((activated_count / code_count) * 100, 2) if code_count > 0 else 0,  # 转化率
                'averageAmount': average_amount,
                'statusData': status_data
            }
            
            sales_list.append(sales_record)

        # 构建返回数据
        result = {
            "code": 0,
            "data": {
                "list": sales_list,
                "total": total
            }
        }

        return jsonify(result)

    except Exception as e:
        print(f"获取销售明细失败: {str(e)}")
        return jsonify({"code": 500, "message": f"获取销售明细失败: {str(e)}"}), 500


def get_new_format_data(agent_id, dimension, time_range, start_datetime, end_datetime):
    """获取新格式的销售统计数据"""
    try:
        # 获取时间段内的销售数据（激活码的使用次数）
        labels = []
        sales_data = []

        # 根据维度生成时间标签和数据
        if dimension == 'day':
            # 按天统计
            current_date = start_datetime
            while current_date < end_datetime:
                next_date = current_date + timedelta(days=1)

                # 添加标签
                labels.append(current_date.strftime('%m-%d'))

                # 查询当天的销售额
                if agent_id:
                    daily_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by == agent_id,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_date,
                        ActivationCode.create_time < next_date
                    ).scalar() or 0
                else:
                    daily_sales = db.session.query(func.count(ActivationCode.id)).filter(
                        ActivationCode.created_by != None,
                        ActivationCode.used_by.isnot(None),
                        ActivationCode.create_time >= current_date,
                        ActivationCode.create_time < next_date
                    ).scalar() or 0

                # 添加数据
                sales_data.append(float(daily_sales) if daily_sales else 0)

                # 移到下一天
                current_date = next_date

        elif dimension in ['week', 'month', 'year']:
            # 复用已有函数获取趋势数据
            trend_resp = get_trend_data(agent_id, dimension, start_datetime, end_datetime)
            trend_data = trend_resp.get_json()['data']
            labels = trend_data['labels']
            sales_data = trend_data['sales']

        # 计算统计数据
        total = sum(sales_data)
        average = round(total / len(sales_data), 2) if sales_data else 0
        max_val = max(sales_data) if sales_data else 0
        min_val = min(sales_data) if sales_data else 0

        # 构建图表数据
        chart_data = []
        for i in range(len(labels)):
            chart_data.append({
                'date': labels[i],
                'value': sales_data[i]
            })

        # 构建返回数据
        result = {
            "total": total,
            "average": average,
            "max": max_val,
            "min": min_val,
            "chartData": chart_data
        }

        return jsonify({"code": 0, "data": result})

    except Exception as e:
        print(f"获取新格式销售统计数据失败: {str(e)}")
        return jsonify({"code": 500, "message": "获取销售统计数据失败"}), 500


@sales_bp.route('/system-analysis', methods=['GET'])
def get_system_sales_analysis():
    """
    获取系统销售分析数据
    ---
    参数:
      - period/dimension: 统计周期(day/week/month/year)，默认month
      - type: 数据类型(overview/trend/ranking)
      - limit: 排名限制数量（用于ranking类型）
      - start_date: 开始日期(YYYY-MM-DD)
      - end_date: 结束日期(YYYY-MM-DD)
    返回:
      - code: 0表示成功
      - data: 根据请求参数返回不同数据
    """
    try:
        # 从请求参数获取
        period = request.args.get('period') or request.args.get('dimension', 'month')
        data_type = request.args.get('type', 'trend')
        limit = int(request.args.get('limit', 10))
        
        # 时间范围
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        # 获取当前日期
        now = datetime.now()
        
        # 如果提供了明确的起止日期，则使用它们
        if start_date:
            start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        else:
            # 默认为过去30天
            start_datetime = now - timedelta(days=30)
        
        if end_date:
            end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        else:
            end_datetime = now + timedelta(days=1)
        
        # 根据数据类型返回不同的结果
        if data_type == 'overview':
            return jsonify({
                "code": 0,
                "data": get_system_overview_data(start_datetime, end_datetime)
            })
        elif data_type == 'trend':
            return jsonify({
                "code": 0,
                "data": get_system_trend_data(period, start_datetime, end_datetime)
            })
        elif data_type == 'ranking':
            return jsonify({
                "code": 0,
                "data": get_system_ranking_data(start_datetime, end_datetime, limit)
            })
        else:
            return jsonify({"code": 1, "message": f"不支持的数据类型: {data_type}"}), 400
            
    except Exception as e:
        print(f"获取系统销售分析数据失败: {str(e)}")
        return jsonify({"code": 500, "message": "获取系统销售分析数据失败"}), 500


def get_system_overview_data(start_datetime, end_datetime):
    """获取系统概览数据"""
    try:
        # 计算总激活数
        total_activations = ActivationCode.query.with_entities(func.count(ActivationCode.id)).filter(
            ActivationCode.status == 1,  # 已使用/已激活
            ActivationCode.create_time >= start_datetime,
            ActivationCode.create_time < end_datetime
        ).scalar() or 0

        # 计算卡密总数
        total_codes = ActivationCode.query.with_entities(func.count(ActivationCode.id)).filter(
            ActivationCode.create_time >= start_datetime,
            ActivationCode.create_time < end_datetime
        ).scalar() or 0

        # 计算代理数量
        total_agents = Agent.query.with_entities(func.count(Agent.id)).filter(
            Agent.create_time >= start_datetime,
            Agent.create_time < end_datetime
        ).scalar() or 0
        
        # 计算激活转化率
        activation_rate = round((total_activations / total_codes) * 100, 2) if total_codes > 0 else 0
        
        # 计算上一个时间段的数据，用于计算趋势
        previous_start = start_datetime - (end_datetime - start_datetime)
        previous_end = start_datetime
        
        # 上一时段的激活数
        previous_activations = db.session.query(func.count(ActivationCode.id)).filter(
            ActivationCode.status == 1,
            ActivationCode.create_time >= previous_start,
            ActivationCode.create_time < previous_end
        ).scalar() or 0
        
        # 上一时段的卡密总数
        previous_codes = db.session.query(func.count(ActivationCode.id)).filter(
            ActivationCode.create_time >= previous_start,
            ActivationCode.create_time < previous_end
        ).scalar() or 0
        
        # 上一时段的代理数量
        previous_agents = db.session.query(func.count(Agent.id)).filter(
            Agent.create_time >= previous_start,
            Agent.create_time < previous_end
        ).scalar() or 0
        
        # 上一时段的激活转化率
        previous_rate = round((previous_activations / previous_codes) * 100, 2) if previous_codes > 0 else 0
        
        # 计算趋势（环比变化百分比）
        activations_trend = round(((total_activations - previous_activations) / previous_activations) * 100, 2) if previous_activations > 0 else 0
        codes_trend = round(((total_codes - previous_codes) / previous_codes) * 100, 2) if previous_codes > 0 else 0
        agents_trend = round(((total_agents - previous_agents) / previous_agents) * 100, 2) if previous_agents > 0 else 0
        activation_rate_trend = round(activation_rate - previous_rate, 2)
        
        return {
            "total_activations": total_activations,
            "total_codes": total_codes,
            "total_agents": total_agents,
            "activation_rate": activation_rate,
            "activations_trend": activations_trend,
            "codes_trend": codes_trend,
            "agents_trend": agents_trend,
            "activation_rate_trend": activation_rate_trend
        }
    except Exception as e:
        print(f"获取系统概览数据失败: {str(e)}")
        return {
            "total_activations": 0,
            "total_codes": 0,
            "total_agents": 0,
            "activation_rate": 0,
            "activations_trend": 0,
            "codes_trend": 0,
            "agents_trend": 0,
            "activation_rate_trend": 0
        }


def get_system_trend_data(period, start_datetime, end_datetime):
    """获取系统趋势数据"""
    try:
        labels = []
        sales = []
        orders = []
        
        # 根据周期生成时间标签和数据
        if period == 'day':
            # 按天统计
            current_date = start_datetime
            while current_date < end_datetime:
                next_date = current_date + timedelta(days=1)
                
                # 添加标签
                labels.append(current_date.strftime('%m-%d'))
                
                # 查询当天的激活数和卡密总数
                daily_sales = db.session.query(func.count(ActivationCode.id)).filter(
                    ActivationCode.status == 1,  # 已使用/已激活
                    ActivationCode.create_time >= current_date,
                    ActivationCode.create_time < next_date
                ).scalar() or 0
                
                daily_orders = db.session.query(func.count(ActivationCode.id)).filter(
                    ActivationCode.create_time >= current_date,
                    ActivationCode.create_time < next_date
                ).scalar() or 0
                
                sales.append(daily_sales)
                orders.append(daily_orders)
                
                # 移动到下一天
                current_date = next_date
                
        elif period == 'week':
            # 按周统计
            # 确保开始日期是周一
            start_week = start_datetime - timedelta(days=start_datetime.weekday())
            current_week = start_week
            
            while current_week < end_datetime:
                next_week = current_week + timedelta(days=7)
                
                # 添加标签 (例如: "06-01 ~ 06-07")
                week_label = f"{current_week.strftime('%m-%d')} ~ {(next_week - timedelta(days=1)).strftime('%m-%d')}"
                labels.append(week_label)
                
                # 查询本周的激活数和卡密总数
                weekly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                    ActivationCode.status == 1,
                    ActivationCode.create_time >= current_week,
                    ActivationCode.create_time < next_week
                ).scalar() or 0
                
                weekly_orders = db.session.query(func.count(ActivationCode.id)).filter(
                    ActivationCode.create_time >= current_week,
                    ActivationCode.create_time < next_week
                ).scalar() or 0
                
                sales.append(weekly_sales)
                orders.append(weekly_orders)
                
                # 移动到下一周
                current_week = next_week
                
        elif period == 'month':
            # 按月统计
            # 确保开始日期是月初
            current_month = datetime(start_datetime.year, start_datetime.month, 1)
            
            while current_month < end_datetime:
                # 计算下个月的第一天
                if current_month.month == 12:
                    next_month = datetime(current_month.year + 1, 1, 1)
                else:
                    next_month = datetime(current_month.year, current_month.month + 1, 1)
                
                # 添加标签 (例如: "2023-06")
                labels.append(current_month.strftime('%Y-%m'))
                
                # 查询本月的激活数和卡密总数
                monthly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                    ActivationCode.status == 1,
                    ActivationCode.create_time >= current_month,
                    ActivationCode.create_time < next_month
                ).scalar() or 0
                
                monthly_orders = db.session.query(func.count(ActivationCode.id)).filter(
                    ActivationCode.create_time >= current_month,
                    ActivationCode.create_time < next_month
                ).scalar() or 0
                
                sales.append(monthly_sales)
                orders.append(monthly_orders)
                
                # 移动到下一月
                current_month = next_month
                
        elif period == 'year':
            # 按年统计
            # 确保开始日期是年初
            current_year = datetime(start_datetime.year, 1, 1)
            
            while current_year < end_datetime:
                next_year = datetime(current_year.year + 1, 1, 1)
                
                # 添加标签 (例如: "2023")
                labels.append(current_year.strftime('%Y'))
                
                # 查询本年的激活数和卡密总数
                yearly_sales = db.session.query(func.count(ActivationCode.id)).filter(
                    ActivationCode.status == 1,
                    ActivationCode.create_time >= current_year,
                    ActivationCode.create_time < next_year
                ).scalar() or 0
                
                yearly_orders = db.session.query(func.count(ActivationCode.id)).filter(
                    ActivationCode.create_time >= current_year,
                    ActivationCode.create_time < next_year
                ).scalar() or 0
                
                sales.append(yearly_sales)
                orders.append(yearly_orders)
                
                # 移动到下一年
                current_year = next_year
        
        return {
            "labels": labels,
            "sales": sales,
            "orders": orders
        }
    except Exception as e:
        print(f"获取系统趋势数据失败: {str(e)}")
        return {
            "labels": [],
            "sales": [],
            "orders": []
        }


def get_system_ranking_data(start_datetime, end_datetime, limit):
    """获取代理排名数据"""
    try:
        # 查询每个代理的激活码使用数量
        agent_sales = db.session.query(
            Agent.id,
            Agent.name,
            func.count(ActivationCode.id).label('sales_count')
        ).join(
            ActivationCode, Agent.id == ActivationCode.created_by
        ).filter(
            ActivationCode.status == 1,  # 已使用/已激活
            ActivationCode.create_time >= start_datetime,
            ActivationCode.create_time < end_datetime
        ).group_by(
            Agent.id
        ).order_by(
            desc('sales_count')
        ).limit(limit).all()
        
        # 提取代理名称和销售数量
        agents = [item.name for item in agent_sales]
        sales = [item.sales_count for item in agent_sales]
        
        # 添加日志输出
        print(f"获取代理排名数据: 代理数量={len(agents)}, 销售数据={sales}")
        
        # 确保至少有一条数据
        if not agents:
            # 如果没有真实数据，添加一些示例数据
            agents = ["暂无代理数据"]
            sales = [0]
        
        return {
            "agents": agents,
            "sales": sales
        }
    except Exception as e:
        print(f"获取代理排名数据失败: {str(e)}")
        return {
            "agents": ["获取失败"],
            "sales": [0]
        }


@sales_bp.route('/export-system', methods=['GET'])
def export_system_sales_data():
    """
    导出系统销售数据
    ---
    参数:
      - period/dimension: 统计周期(day/week/month/year)，默认month
      - start_date: 开始日期(YYYY-MM-DD)
      - end_date: 结束日期(YYYY-MM-DD)
    返回:
      - Excel文件
    """
    try:
        # 从请求参数获取
        start_date = request.args.get('start_date')
        end_date = request.args.get('end_date')
        
        # 获取当前日期
        now = datetime.now()
        
        # 如果提供了明确的起止日期，则使用它们
        if start_date:
            start_datetime = datetime.strptime(start_date, '%Y-%m-%d')
        else:
            # 默认为过去30天
            start_datetime = now - timedelta(days=30)
        
        if end_date:
            end_datetime = datetime.strptime(end_date, '%Y-%m-%d') + timedelta(days=1)
        else:
            end_datetime = now + timedelta(days=1)
        
        # 查询系统销售数据
        query = db.session.query(
            func.date(ActivationCode.create_time).label('date'),
            func.count(ActivationCode.id).label('total_codes'),
            func.sum(case(
                (ActivationCode.status == 1, 1),
                else_=0
            )).label('activated_codes')
        ).filter(
            ActivationCode.create_time >= start_datetime,
            ActivationCode.create_time < end_datetime
        ).group_by(
            func.date(ActivationCode.create_time)
        ).order_by(
            desc('date')
        )
        
        # 执行查询
        results = query.all()
        
        # 创建Excel文件
        output = BytesIO()
        workbook = xlsxwriter.Workbook(output)
        worksheet = workbook.add_worksheet('系统销售数据')
        
        # 添加表头
        headers = ['日期', '卡密总数', '已激活数', '未激活数', '激活率', '新增用户']
        for col, header in enumerate(headers):
            worksheet.write(0, col, header)
        
        # 添加数据
        for row, result in enumerate(results, 1):
            date_str = result.date.strftime('%Y-%m-%d') if result.date else ''
            total_codes = result.total_codes or 0
            activated_codes = result.activated_codes or 0
            unused_codes = total_codes - activated_codes
            activation_rate = f"{round((activated_codes / total_codes) * 100, 2)}%" if total_codes > 0 else "0%"
            
            # 查询该日期新增用户数
            new_users = db.session.query(func.count(User.id)).filter(
                func.date(User.create_time) == result.date
            ).scalar() or 0
            
            worksheet.write(row, 0, date_str)
            worksheet.write(row, 1, total_codes)
            worksheet.write(row, 2, activated_codes)
            worksheet.write(row, 3, unused_codes)
            worksheet.write(row, 4, activation_rate)
            worksheet.write(row, 5, new_users)
        
        # 添加汇总行
        summary_row = len(results) + 1
        worksheet.write(summary_row, 0, '总计')
        worksheet.write(summary_row, 1, f'=SUM(B2:B{summary_row})')
        worksheet.write(summary_row, 2, f'=SUM(C2:C{summary_row})')
        worksheet.write(summary_row, 3, f'=SUM(D2:D{summary_row})')
        worksheet.write(summary_row, 4, f'=C{summary_row+1}/B{summary_row+1}')
        worksheet.write(summary_row, 5, f'=SUM(F2:F{summary_row})')
        
        # 关闭工作簿
        workbook.close()
        
        # 设置响应头
        output.seek(0)
        filename = f'系统销售数据_{datetime.now().strftime("%Y%m%d%H%M%S")}.xlsx'
        
        return send_file(
            output,
            mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
            as_attachment=True,
            download_name=filename
        )
        
    except Exception as e:
        print(f"导出系统销售数据失败: {str(e)}")
        return jsonify({"code": 500, "message": "导出系统销售数据失败"}), 500


# 注册蓝图
def init_app(app):
    app.register_blueprint(sales_bp, url_prefix='/api/admin/mp/sales')
